/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tamnhat.report.model;

import com.tamnhat.report.entity.ReportP1;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import telsoft.admin.util.AppServer;
import telsoft.sql.Database;

/**
 *
 * @author Administrator
 */
public class ReportP2Model {

    public static ArrayList<ReportP1> getReport(String tuNgay, String denNgay, int doiTac) throws Exception {
        ArrayList<ReportP1> list = new ArrayList<ReportP1>();
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = AppServer.getConnection();
            st = conn.createStatement();
            String strSQL = "SELECT   cmdcode, telco_id, COUNT ( * ) sub_count FROM   cms_friend.mo_queue WHERE send_date >= to_date('" + tuNgay + "','dd/MM/yyyy') AND send_date < to_date('" + denNgay + "','dd/MM/yyyy') + 1 AND telco_id IS NOT NULL AND cmdcode not IN ('CAT') AND (second_partner_id = " + doiTac + ") GROUP BY ROLLUP (cmdcode,telco_id)";
            //ResultSet rs = st.executeQuery("SELECT cmdcode, COUNT ( * ) sub_count FROM   cms_admin.mo_queue WHERE send_date >= to_date('"+tuNgay+"','dd/MM/yyyy') AND send_date < to_date('"+denNgay+"','dd/MM/yyyy') + 1 AND telco_id IS NOT NULL AND cmdcode IN ('SOI', 'XS') AND (second_partner_id = 2 OR '1' IS NULL) GROUP BY   ROLLUP (cmdcode)");
            rs = st.executeQuery(strSQL);
            while (rs.next()) {
                ReportP1 rp = new ReportP1();
                if (rs.getString(1) == null) {
                    rp.setTongcong(Integer.parseInt(rs.getString(3)));
                } else {
                    rp.setMaDV(rs.getString(1));
                    if (rs.getString(2) == null) {
                        rp.setMang("");
                    } else {
                        rp.setMang(rs.getString(2));
                    }
                    rp.setSoluong(Integer.parseInt(rs.getString(3)));
                }
                list.add(rp);
            }
            return list;
        } finally {
            Database.closeObject(conn);
            Database.closeObject(rs);
            Database.closeObject(st);
        }
    }
}
